package tr.com.argela.tap.mot.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import tr.com.argela.tap.mot.logger.LoggerFactory;
import tr.com.argela.tap.mot.models.Module;
import tr.com.argela.tap.mot.models.Project;
import tr.com.argela.tap.mot.models.TestCase;
import tr.com.argela.tap.mot.models.TestPoint;
import tr.com.argela.tap.mot.models.User;
import tr.com.argela.tap.mot.models.Version;
import tr.com.argela.tap.mot.models.VersionModule;
import tr.com.argela.tap.mot.models.VersionTestCase;
import tr.com.argela.tap.mot.models.VersionTestPoint;

public class VersionDao {

    private static Logger logger = LoggerFactory.getLogger("servlets");

    public VersionDao() {

    }

    public int addProjectVersion(DbManager dbManager, String name, String desc, Project project, Date relase) {

        PreparedStatement stmt = null;
        int effectedRows = 0;

        String sql = "insert into version (name,description,project_id,relase_date) values (?,?,?,?)";
        System.out.println("Query: " + sql);
        try {
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setString(1, name);
            stmt.setString(2, desc);
            stmt.setLong(3, project.getId());
            stmt.setDate(4, new java.sql.Date(relase.getTime()));
            effectedRows = stmt.executeUpdate();

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

        }

        return effectedRows;
    }

    public List<Version> seeVersions() {
        Version version = null;
        PreparedStatement stmt = null;
        List<Version> versionList = new ArrayList<Version>();
        String sql = "select * from version ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);

            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                version = new Version();

                version.setId(rs.getLong("id"));
                version.setName(rs.getString("name"));
                version.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("project_id"));
                version.setProject(project);
                version.setRelease(rs.getDate("relase_date"));
                versionList.add(version);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (version == null) {
            System.out.println("Version is null: ");
        }
        return versionList;
    }

    public List<Version> seethisVersions(Long project_id) {
        Version version = null;
        PreparedStatement stmt = null;
        List<Version> versionList = new ArrayList<Version>();
        String sql = "select * from version where project_id= ? ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, project_id);
            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                version = new Version();

                version.setId(rs.getLong("id"));
                version.setName(rs.getString("name"));
                version.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("project_id"));
                version.setProject(project);
                version.setRelease(rs.getDate("relase_date"));
                versionList.add(version);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (version == null) {
            System.out.println("Version is null: ");
        }
        return versionList;
    }

    public Version checkversions(Long id) {
        Version version = null;
        PreparedStatement stmt = null;

        String sql = "select * from version where id= ? ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                version = new Version();

                version.setId(rs.getLong("id"));
                version.setName(rs.getString("name"));

                version.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("project_id"));
                version.setProject(project);
                version.setRelease(rs.getDate("relase_date"));

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (version == null) {
            System.out.println("Version is null: ");
        }
        return version;
    }

    public List<VersionModule> SeeVersionModules(Long module_id) {

        VersionModule versionmodule = null;
        PreparedStatement stmt = null;
        List<VersionModule> versionmoduleList = new ArrayList<VersionModule>();
        String sql = "select * from version_module where module_id = ? ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, module_id);
            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                versionmodule = new VersionModule();

                versionmodule.setId(rs.getLong("id"));
                versionmodule.setName(rs.getString("name"));
                User user = new User();
                user.setId(rs.getLong("user_id"));
                versionmodule.setUser(user);
                versionmodule.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("project_id"));
                versionmodule.setProject(project);
                Module module = new Module();
                module.setId(rs.getLong("module_id"));
                versionmodule.setModule(module);
                Version version = new Version();
                version.setId(rs.getLong("version_id"));
                versionmodule.setVersion(version);
                versionmoduleList.add(versionmodule);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (versionmodule == null) {
            System.out.println("VersionModule is null: ");
        }
        return versionmoduleList;

    }

    public VersionModule CheckVersionModules(Long id) {

        VersionModule versionmodule = null;
        PreparedStatement stmt = null;

        String sql = "select vm.id as vermodid, vm.name as vermodname, u.id as user_id, u.name as username, vm.description, p.id as pr_id, p.name as pr_name, m.id as module_id, m.name as module_name, v.id as version_id, v.name as version_name "
                + "from version_module vm, user u, project p, module m, version v where vm.id = ? and u.id=vm.user_id and p.id=vm.project_id and m.id=vm.module_id and v.id=vm.version_id";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                versionmodule = new VersionModule();

                versionmodule.setId(rs.getLong("vermodid"));
                versionmodule.setName(rs.getString("vermodname"));
                User user = new User();
                user.setId(rs.getLong("user_id"));
                user.setName(rs.getString("username"));
                versionmodule.setUser(user);
                versionmodule.setDescription(rs.getString("description"));
                Project project = new Project();
                project.setId(rs.getLong("pr_id"));
                project.setName(rs.getString("pr_name"));
                versionmodule.setProject(project);
                Module module = new Module();
                module.setId(rs.getLong("module_id"));
                module.setName(rs.getString("module_name"));
                versionmodule.setModule(module);
                Version version = new Version();
                version.setId(rs.getLong("version_id"));
                version.setName(rs.getString("version_name"));
                versionmodule.setVersion(version);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (versionmodule == null) {
            System.out.println("VersionModule is null: ");
        }
        return versionmodule;

    }

    public VersionTestPoint CheckVersionTestP(Long id) {

        VersionTestPoint versionpoint = null;
        PreparedStatement stmt = null;

        String sql = "select vp.id as verpoid, vp.name as verponame,m.id as module_id, m.name as module_name, u.id as user_id, u.name as username, vp.description, p.id as po_id, p.name as po_name,  v.id as version_id, v.name as version_name "
                + "from version_test_point vp, module m, user u, test_point p,  version v where vp.id = ? and u.id=vp.user_id and p.id=vp.test_point_id and m.id=vp.module_id and v.id=vp.version_id";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                versionpoint = new VersionTestPoint();

                versionpoint.setId(rs.getLong("verpoid"));
                versionpoint.setName(rs.getString("verponame"));
                Module module = new Module();
                module.setId(rs.getLong("module_id"));
                module.setName(rs.getString("module_name"));
                versionpoint.setModule(module);
                User user = new User();
                user.setId(rs.getLong("user_id"));
                user.setName(rs.getString("username"));
                versionpoint.setUser(user);
                versionpoint.setDescription(rs.getString("description"));
                TestPoint testpoint = new TestPoint();
                testpoint.setId(rs.getLong("po_id"));
                testpoint.setName(rs.getString("po_name"));
                versionpoint.setTestpoint(testpoint);

                Version version = new Version();
                version.setId(rs.getLong("version_id"));
                version.setName(rs.getString("version_name"));
                versionpoint.setVersion(version);

            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (versionpoint == null) {
            System.out.println("VersionTestPoint is null: ");
        }
        return versionpoint;

    }

    public List<VersionTestPoint> SeeVersionTestPoint(Long test_point_id) {

        VersionTestPoint versiontestpoint = null;
        PreparedStatement stmt = null;
        List<VersionTestPoint> versionpointList = new ArrayList<VersionTestPoint>();
        String sql = "select * from version_test_point where test_point_id =? ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, test_point_id);
            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                versiontestpoint = new VersionTestPoint();

                versiontestpoint.setId(rs.getLong("id"));
                versiontestpoint.setName(rs.getString("name"));
                Module module = new Module();
                module.setId(rs.getLong("module_id"));
                versiontestpoint.setModule(module);
                User user = new User();
                user.setId(rs.getLong("user_id"));
                versiontestpoint.setUser(user);
                versiontestpoint.setDescription(rs.getString("description"));
                TestPoint testpoint = new TestPoint();
                testpoint.setId(rs.getLong("test_point_id"));
                versiontestpoint.setTestpoint(testpoint);
                Version version = new Version();
                version.setId(rs.getLong("version_id"));
                versiontestpoint.setVersion(version);
                versionpointList.add(versiontestpoint);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (versiontestpoint == null) {
            System.out.println("VersionTestPoint is null: ");
        }
        return versionpointList;

    }

    public List<VersionTestCase> SeeVersionTestCases(Long test_case_id) {

        VersionTestCase versiontestcase = null;
        PreparedStatement stmt = null;
        List<VersionTestCase> versiontestcaseList = new ArrayList<VersionTestCase>();
        String sql = "select * from version_test_case where test_point_id =? ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, test_case_id);
            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                versiontestcase = new VersionTestCase();

                versiontestcase.setId(rs.getLong("id"));
                versiontestcase.setName(rs.getString("name"));
                versiontestcase.setBreq("breq");
                TestPoint testpoint = new TestPoint();
                testpoint.setId(rs.getLong("test_point_id"));
                versiontestcase.setTestPoint(testpoint);
                versiontestcase.setPrecondition("precondition");
                versiontestcase.setDescription("test_definition");
                versiontestcase.setExpectedRes("expected_result");
                versiontestcase.setCheck("check_item");
                versiontestcase.setResult("result");
                versiontestcase.setNotes("notes");
                versiontestcase.setArtno("art_no");
                User user = new User();
                user.setId(rs.getLong("user_id"));
                versiontestcase.setUser(user);

                Version version = new Version();
                version.setId(rs.getLong("version_id"));
                versiontestcase.setVersion(version);
                versiontestcaseList.add(versiontestcase);
                TestCase testcase = new TestCase();
                testcase.setId(rs.getLong("test_case_id"));
                versiontestcaseList.add(versiontestcase);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (versiontestcase == null) {
            System.out.println("VersionModule is null: ");
        }
        return versiontestcaseList;

    }

    public VersionTestCase CheckVersionTestC(Long id) {

        VersionTestCase versiontestcase = null;
        PreparedStatement stmt = null;

        String sql = "select vt.id as vercaseid , vt.name as vercasename , vt.breq , p.id as pointid , p.name as pointname , vt.precondition , vt.test_definition , vt.expected_result , vt.check_item , "
                + "vt.result , vt.notes , vt.art_no , u.id as user_id , u.name as username , v.id as verid , v.name as vername , t.id as caseid , t.name as casename "
                + "from version_test_case vt , test_point p , user u , version v , test_case t "
                + "where vt.id = ? and p.id = vt.test_point_id and u.id = vt.user_id and v.id = vt.version_id and t.id = vt.test_case_id ";
        System.out.println("Query: " + sql);
        ResultSet rs;
        DbManager dbManager = null;
        try {
            dbManager = new DbManager();
            stmt = dbManager.getConn().prepareStatement(sql);
            stmt.setLong(1, id);
            rs = stmt.executeQuery();
            System.out.println("Rs size: " + rs.getRow());
            while (rs.next()) {
                versiontestcase = new VersionTestCase();

                versiontestcase.setId(rs.getLong("vercaseid"));
                versiontestcase.setName(rs.getString("vercasename"));
                versiontestcase.setBreq("breq");
                TestPoint testpoint = new TestPoint();
                testpoint.setId(rs.getLong("pointid"));
                testpoint.setName(rs.getString("pointname"));
                versiontestcase.setTestPoint(testpoint);
                versiontestcase.setPrecondition("precondition");
                versiontestcase.setDescription("test_definition");
                versiontestcase.setExpectedRes("expected_result");
                versiontestcase.setCheck("check_item");
                versiontestcase.setResult("result");
                versiontestcase.setNotes("notes");
                versiontestcase.setArtno("art_no");
                User user = new User();
                user.setId(rs.getLong("user_id"));
                user.setName(rs.getString("username"));
                versiontestcase.setUser(user);

                Version version = new Version();
                version.setId(rs.getLong("verid"));
                version.setName(rs.getString("vername"));
                versiontestcase.setVersion(version);

                TestCase testcase = new TestCase();
                testcase.setId(rs.getLong("caseid"));
                testcase.setName(rs.getString("casename"));
                versiontestcase.setTestcase(testcase);
            }

        } catch (SQLException ex) {
            System.out.println("Error: " + ex);
            ex.printStackTrace();
            logger.error("Error", ex);
        } finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            dbManager.close();
        }

        if (versiontestcase == null) {
            System.out.println("VersionModule is null: ");
        }
        return versiontestcase;

    }
}
